# -*- coding: utf-8 -*-
'''
@Time : 2021/5/9 15:10
@description:
@Author : yin
@Email : yylai24@163.com
@File : CheckScore.py
@Project : check_score
'''

import pandas as pd
import configparser
import os
import json
from tqdm import tqdm

config = configparser.ConfigParser()
config.read("config.ini",encoding='utf-8')

class CheckScore:
    def __init__(self):
        self.excel_path_dir = config["path"]["allExcelDir"]
        self.jwch_excel_path = config["path"]["jwchExcelPath"]
        self.jx_excel_path = config["path"]["jxExcelPath"]
        self.resultPath = config["path"]["resultPath"]
        self.resultPath_req = config["path"]["resultPath_req"]
        self.regex = "(1+3)"
        self.regex2 = "00.xlsx"           #-----------需要确定顺序表的位置

    # 判断是否是002.xlsx表
    def get_part_all_student(self,path):

        if path.find(self.regex2) != -1:
            return True
        else:
            return False

    # 判断页码的长度是否为3
    def change_page(self,page):
        if len(page) == 2:
            page = "0" + page
        else:
            page = "00" + page
        return page

    # 002.xlsx表的处理
    def del_part_all_student(self,path):
        student_part_all = pd.read_excel(path, skiprows=[0], header=0, dtype=object)
        student_record = path.split("\\")[-1].split("-")[:-1]
        student_record_position = "-".join(student_record)  # 学生的总卷前编号
        student_part_one = student_part_all.loc[:, ['页码', '学号', '姓名']]
        student_part_one.rename(columns={"页码": "page", "学号": "student_id", "姓名": "student_name"}, inplace=True)
        student_part_two = student_part_all.loc[:, ['页码.1', '学号.1', '姓名.1']]
        student_part_two.rename(columns={"页码.1": "page", "学号.1": "student_id", "姓名.1": "student_name"}, inplace=True)
        student_part = pd.concat([student_part_one, student_part_two], ignore_index=True, axis=0)
        student_part.dropna(axis=0, inplace=True)
        student_part['page'] = student_part['page'].apply(self.change_page)
        student_part_json = json.loads(
            pd.DataFrame(data=student_part.values.T, columns=student_part.index, index=student_part.columns).to_json())
        # student_part_json=map(new_student_cord,student_record_position,student_part_json)
        student_list = []
        for i in student_part_json:
            student_part_json[i]['page'] = student_record_position + "-" + student_part_json[i]['page']
            student_list.append(student_part_json[i])
        return student_list

    # 检查数据是否正确
    def error_student_record(self,full_student, student_list):
        error = []
        for student_demo in student_list:
            full_data_name = full_student.loc[full_student['page'] == student_demo['page'], "student_name"].to_list()[0]
            if full_data_name != student_demo['student_name']:
                error.append(student_demo)
        return error

    # 判断错误信息是否存在
    def isempty_data(self,error):
        if len(error) == 0:
            return False
        else:
            return True

    # 将错误信息进行输出
    def save_error_req(self,error, path):
        if self.isempty_data(error):
            data = pd.DataFrame(data=error, columns=['page', 'student_id', 'student_name'])
            data.to_excel(path, index=None)

    def get_exist(self,class_id):

        if class_id.find(self.regex) != -1:  # 修改过-----------------------
            # print(class_id.replace("(1+3)",""))
            return class_id.replace("(1+3)", "")
        else:
            return class_id

    def apply_func(self,x):
        x = self.get_exist(x)
        x = x.replace(" ", "")
        return x

    def student_info(self,path):
        s_info = pd.read_excel(path, header=None, dtype=object)

        # for i in s_info.columns:
        #     if s_info[i].isnull().all():
        #         s_info.drop(columns=i, axis=1, inplace=True)
        # for i in s_info.index:
        #     if s_info[i].isnull().all():
        #         s_info.drop(index=i, axis=0, inplace=True)
        for i in s_info.columns:
            if s_info[i].isnull().all():
                s_info.drop(columns=i, axis=1, inplace=True)
        student_class_id = self.get_exist(s_info.iloc[1, 7])

        info = {
            'student_name': s_info.iloc[0, 3],
            'student_id': s_info.iloc[0, 1],
            'student_class_id': student_class_id
        }

        student_data = pd.DataFrame(data=info, index=[0])
        student_data['flag'] = "检查表"
        return student_data

    # 读取教务处的总表数据
    def get_jwch_info(self,path):
        jwch_data_sheet = pd.read_excel(path, sheet_name=None)
        jwch_all_data = pd.DataFrame(columns=["student_name", "student_id", "student_class_id"])
        for i in jwch_data_sheet:
            jwch_data = pd.read_excel(path, sheet_name=i, dtype=object)
            if set(['姓名', '学号', '班级']).issubset(set(jwch_data.columns)):
                # jwch_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
                jwch_data = jwch_data.loc[:, ['姓名', '学号', '班级']]
                jwch_data.rename(columns={"姓名": "student_name", "学号": "student_id", "班级": "student_class_id"},
                                 inplace=True)
            if set(['姓名', '学号', '班号']).issubset(set(jwch_data.columns)):
                # jwch_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
                jwch_data = jwch_data.loc[:, ['姓名', '学号', '班号']]
                jwch_data.rename(columns={"姓名": "student_name", "学号": "student_id", "班号": "student_class_id"},
                                 inplace=True)
                # 将数据进行去空
            jwch_data['student_name'] = jwch_data['student_name'].astype(str).apply(lambda x: x.replace(" ", ""))
            jwch_data['student_id'] = jwch_data['student_id'].astype(str).apply(lambda x: x.replace(" ", ""))
            jwch_data['student_class_id'] = jwch_data['student_class_id'].astype(str).apply(self.apply_func)
            jwch_data['student_class_id'] = jwch_data['student_class_id'].astype(str).apply(lambda x: x.replace("(", "（"))
            jwch_data['student_class_id'] = jwch_data['student_class_id'].astype(str).apply(lambda x: x.replace(")", "）"))
            jwch_all_data = pd.concat([jwch_all_data, jwch_data], ignore_index=True, axis=0)
        jwch_all_data['flag'] = "来自jwch"
        return jwch_all_data

    def get_jx_info(self,path):
        jx_data_sheet = pd.read_excel(path, sheet_name=None)
        jx_all_data = pd.DataFrame(columns=["student_name", "student_id", "student_class_id"])
        for i in jx_data_sheet:
            jx_data = pd.read_excel(path, sheet_name=i, dtype=object)
            if set(['档号', '题名', '学号', '班级']).issubset(set(jx_data.columns)):
                # jx_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
                jx_data = jx_data.loc[:, ['档号', '题名', '学号', '班级']]
                jx_data.rename(
                    columns={'档号': 'page', "题名": "student_name", "学号": "student_id", "班级": "student_class_id"},
                    inplace=True)
                # 删除空数据所在的行
                jx_data.dropna(axis=0, inplace=True)
                # 去空
                jx_data['student_name'] = jx_data['student_name'].astype(str).apply(lambda x: x.replace(" ", ""))
                jx_data['student_id'] = jx_data['student_id'].astype(str).apply(lambda x: x.replace(" ", ""))
                jx_data['student_class_id'] = jx_data['student_class_id'].astype(str).apply(self.apply_func)
                jx_all_data = pd.concat([jx_all_data, jx_data], axis=0, ignore_index=True)
        jx_all_data['flag'] = '来自jx表'
        return jx_all_data




    # 检查数据
    def check_data(self,full_data, check_data):

        check_data['student_id'] = check_data['student_id'].astype(str).replace(" ","")
        check_data['student_class_id'] = check_data['student_class_id'].astype(str).apply(lambda x: x.replace("(师)", ""))
        check_data['student_class_id'] = check_data['student_class_id'].astype(str).apply(lambda x: x.replace("（师）", ""))
        check_data['student_name'] = check_data['student_name'].astype(str).apply(lambda x: x.replace("·", ""))
        check_data['student_name'] = check_data['student_name'].astype(str).apply(lambda x: x.replace("•", ""))
        check_data['student_name'] = check_data['student_name'].astype(str).apply(lambda x: x.replace(" ", ""))
        student_id = check_data.loc[0, 'student_id']
        student_name = check_data.loc[0, 'student_name']
        if len(student_id)!=0 &len(student_name)!=0:
        # 根据学生的学号进行查询
            student_full = full_data.loc[
                full_data['student_id'] == student_id & full_data['student_name']==student_name, ['student_name', 'student_id', 'student_class_id', 'flag']]
        elif len(student_id)==0 &len(student_name)!=0:
            student_full=full_data.loc[
                full_data['student_name']==student_name,['student_name', 'student_id', 'student_class_id', 'flag']
            ]
        elif len(student_id)!=0 &len(student_name)==0:
            student_full = full_data.loc[
                full_data['student_id'] == student_id, ['student_name', 'student_id', 'student_class_id', 'flag']
            ]
        else:
            pass

        # 将两个数据进行合并
        data = pd.concat([student_full, check_data], axis=0, ignore_index=True)
        data['student_name']=data['student_name'].astype(str).apply(lambda x:x.replace(" ",""))
        # data['student_name']=data['student_name'].apply(lambda x:x.replace("·",""))
        # data['student_name']=data['student_name'].apply(lambda x:x.replace("•",""))
        data['student_id']=data['student_id'].astype(str).apply(lambda x:x.replace(" ",""))
        data['student_class_id']=data['student_class_id'].astype(str).apply(lambda x:x.replace(" ",""))

        # 将数据表进行去重
        data.drop_duplicates(subset=['student_name', 'student_id', 'student_class_id'], keep=False, inplace=True)
        return data

    def save_error(self, data, path):
        # print(data)
        if not data.empty:
            data.to_excel(path, index=None)

    def get_all_excel(self,rootdir):
        """
        获取某文件夹底下所有Excel表格的路径
        :param rootdir: 文件夹路径
        :return: Excel表格路径列表
        """
        excel_path_list = []
        for root, dirs, files in os.walk(rootdir):
            for file in files:
                if file.endswith(".xlsx"):
                    # if file.split('-')[-1].split('.')[0]=='001' or file.split('-')[-1].split('.')[0]=='002':
                    if file.split('-')[-1].split('.')[0]=='001':
                        continue
                    file_path = os.path.join(root, file)
                    excel_path_list.append(file_path)
        return excel_path_list

    def run(self):
        excel_path_list = self.get_all_excel(self.excel_path_dir)
        data1 = self.get_jwch_info(self.jwch_excel_path)

        data1['student_id'] = data1['student_id'].astype(str).apply(lambda x: x.replace(" ", ""))
        data1['student_name'] = data1['student_name'].astype(str).apply(lambda x: x.replace("·", ""))
        data1['student_name'] = data1['student_name'].astype(str).apply(lambda x: x.replace("•", ""))
        data1['student_name'] = data1['student_name'].astype(str).apply(lambda x: x.replace(" ", ""))
        data1['student_class_id'] = data1['student_class_id'].astype(str).apply(lambda x: x.replace("（师）", ""))
        data1['student_class_id'] = data1['student_class_id'].astype(str).apply(lambda x: x.replace(" ", ""))
        # 有jx表之后打开
        # data2 = self.get_jx_info(self.jx_excel_path)
        if not os.path.exists(self.resultPath):
            os.mkdir(self.resultPath)
        #有jx表之后打开
        # if not os.path.exists(self.resultPath_req):
        #     os.mkdir(self.resultPath_req)
        for path in tqdm(excel_path_list):
            try:

                if self.get_part_all_student(path):
                    check_student = self.del_part_all_student(path)
                    # 有jx表之后打开
                    # error_sequence = self.error_student_record(data2, check_student)
                    # self.save_error_req(error_sequence, "{}/{}_jx.xlsx".format(self.resultPath_req,os.path.basename(path)))
                else:
                    student_info = self.student_info(path)
                    error1 = self.check_data(data1, student_info)
                    # 有jx表之后打开
                    # error2 = self.check_data(data2, student_info)
                    # 将数据进行保存
                    self.save_error(error1, "{}/{}_jwch.xlsx".format(self.resultPath, os.path.basename(path)))
                    # 有jx表之后打开
                    #self.save_error(error2, "{}/{}_jx.xlsx".format(self.resultPath, os.path.basename(path)))
            except Exception as e:
                with open("{}/error.log".format(self.resultPath),"a+",encoding="utf-8") as tf:
                    tf.write(str(e)+"\t"+path+"\n")